package edu.clemson.wcbeck.cft;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class MyDatabaseHelper extends SQLiteOpenHelper {

	public static final String[] COLUMNS_CONF = {"_id", "full_name", "name"};
	public static final String[] COLUMNS_TEAMS = {"_id", "conf_id", "name", "full_name", "favorite"};
	public static final String[] COLUMNS_MEDIA = {"_id","teamid","name","location","comment","gps_lat","gps_lon","mediaType","isStock","isUploaded","date_time"};
	public static final String[] TABLES = {"conferences", "teams", "media"};
	public static final String[] CONFS = {"ACC", "Big 12", "Big East", "Big Ten", 
		"C-USA", "I-A Indeps", "MAC", "MW", "Pac-12", "SEC", "Sun Belt", "WAC"};
	
	public static final String SELECT_ALL_TEAMS = "SELECT * FROM teams";
	public static final String SELECT_ALL_CONFS = "SELECT * FROM conferences";
	public static final String SELECT_TEAMS_BY_CONF = "SELECT name FROM teams WHERE conf_id = ";
	public static final String SELECT_FAV_TEAMS = "SELECT * FROM teams WHERE favorite = 1";
	public static final String SELECT_IMG_MEDIA = "SELECT * FROM media WHERE mediaType = 0";
	public static final String SELECT_VID_MEDIA = "SELECT * FROM media WHERE mediaType = 1";
	public static final String SELECT_AUD_MEDIA = "SELECT * FROM media WHERE mediaType = 2";
	public static final String SELECT_ALL_MEDIA = "SELECT * FROM media";
	public static final String TOGGLE_FAVORITE = "UPDATE favorite FROM teams WHERE _id = ";
	
	//The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/edu.clemson.wcbeck.cft/databases/";
    private static String DB_NAME = "CFTDatabaseFixed.db";
    private static final int DB_VERSION = 1;
 
    private SQLiteDatabase myDataBase; 
    private final Context myContext;
    private Toast myToast;
	private CharSequence toastText;
    
 
    public MyDatabaseHelper(Context context) {
 
    	super(context, DB_NAME, null, DB_VERSION);
        this.myContext = context;
    }	
    
    public MyDatabaseHelper(Context context, String name, int version) {
    	 
    	super(context, name, null, version);
        this.myContext = context;
    }
 
    public void createDataBase() throws IOException{
 
    	boolean dbExist = checkDataBase();
 
    	if(dbExist){ //do nothing - database already exists
    	}else{
        	this.getReadableDatabase(); //Creates an empty database to write into
 
        	try {
    			copyDataBase();
    		} catch (IOException e) {
        		throw new Error("Error copying database");
        	}
    	}
    }
 
    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */
    private boolean checkDataBase(){
 
    	SQLiteDatabase checkDB = null;
 
    	try{
    		String myPath = DB_PATH + DB_NAME;
    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    	}catch(SQLiteException e){ /*database does't exist yet.*/ }
 
    	if(checkDB != null){
    		checkDB.close();
    	}
 
    	return checkDB != null ? true : false;
    }
 
    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transferring bytestream.
     * */
    private void copyDataBase() throws IOException{
 
    	//Open your local db as the input stream
    	InputStream myInput = myContext.getAssets().open(DB_NAME);
    	String outFileName = DB_PATH + DB_NAME;
    	OutputStream myOutput = new FileOutputStream(outFileName);
 
    	//transfer bytes from the inputfile to the outputfile
    	byte[] buffer = new byte[1024];
    	int length;
    	while ((length = myInput.read(buffer))>0){
    		myOutput.write(buffer, 0, length);
    	}
 
    	//Close the streams
    	myOutput.flush();
    	myOutput.close();
    	myInput.close();
    }
 
    public void openDataBase() throws SQLException{
    	//Open the database
        String myPath = DB_PATH + DB_NAME;
    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
	    if (!myDataBase.isReadOnly()) {
	        // Enable foreign key constraints
	        myDataBase.execSQL("PRAGMA foreign_keys=ON;");
	    }
    }
 
    @Override
	public synchronized void close() {
 
    	    if(myDataBase != null)
    		    myDataBase.close();

    	    super.close();
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {
 
	}
 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
	}
 
	public Team createTeam(int id){
		Team newTeam;// = new Team();
		String query = "SELECT * FROM " + TABLES[1] + " WHERE _id = " + id + ";";
		
		Cursor c = myDataBase.rawQuery(query, null);
    	c.moveToFirst();
    	
    	newTeam = cursorToTeam(c);
    	
    	c.close();
    	return newTeam;
	}
	
	public boolean toggleFavorite(Team team) {
		int id = team.getId();
		int favorite = team.isFavorite();
		ContentValues values = new ContentValues();
		
		if (favorite == 1) {
			values.put("favorite", 0);
			toastText = team.getName() + " has been removed!";
		}
		else {
			values.put("favorite", 1);
			toastText = team.getName() + " has been added!";
		}
			
		int toggled = myDataBase.update(TABLES[1], values, COLUMNS_TEAMS[0] + "=" +id, null);
		
		myToast = Toast.makeText(myContext, toastText, Toast.LENGTH_SHORT);
		myToast.show();
		
		return toggled == 1;
	}
	
	public List<Team> getAllTeams(){	
		List<Team> teams = new ArrayList<Team>();
		Cursor cursor = myDataBase.rawQuery(SELECT_ALL_TEAMS, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Team team = cursorToTeam(cursor);
			teams.add(team);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return teams;
	}
	
	public String[] getTeamsByConf(int conf_id){		
		Cursor cursor = myDataBase.rawQuery(SELECT_TEAMS_BY_CONF + conf_id, null);
		String[] str;
		
		if(cursor.getCount() > 0){
            str = new String[cursor.getCount()];
            int i = 0;
 
            while (cursor.moveToNext()){
                 str[i] = cursor.getString(0);
                 i++;
            }
        }else{ str = new String[] {}; } //If nothing is returned
		
		cursor.close();
		return str;
	}
	
	public int getTeamIdByName(String[] name){
		Cursor c = myDataBase.rawQuery("SELECT _id FROM teams WHERE name = ?", name);
		c.moveToFirst();
		
		int id = c.getInt(0);
		c.close();
		return id;		
	}
	
	public String[] getAllConfs(){		
		Cursor cursor = myDataBase.rawQuery(SELECT_ALL_CONFS, null);
		String[] str;
		
		if(cursor.getCount() > 0){
            str = new String[cursor.getCount()];
            int i = 0;
 
            while (cursor.moveToNext()){
                 str[i] = cursor.getString(1);
                 i++;
            }
        }else{ str = new String[] {}; } //If nothing is returned
		
		cursor.close();
		return str;
	}
	
	public List<Team> getAllFavTeams() {
		List<Team> teams = new ArrayList<Team>();
		Cursor cursor = myDataBase.rawQuery(SELECT_FAV_TEAMS, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Team team = cursorToTeam(cursor);
			teams.add(team);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return teams;
	}
	
	private Team cursorToTeam(Cursor cursor) {
		Team team = new Team();
		team.setId(cursor.getInt(0));
		team.setConfId(cursor.getInt(1));
		team.setName(cursor.getString(2));
		team.setFullName(cursor.getString(3));
		team.setFavorite(cursor.getInt(4));
		return team;
	}
	
	public long addMedia(MediaFile mediaFile){
		ContentValues values = mediaFile.mediaToConVal();
		return myDataBase.insert(TABLES[2], null, values);
	}
	
	public MediaFile getMediaFile(int id){
		MediaFile newMedia;
		String query = "SELECT * FROM " + TABLES[2] + " WHERE _id = " + id + ";";
		
		Cursor c = myDataBase.rawQuery(query, null);
    	c.moveToFirst();
    	
    	newMedia = cursorToMedia(c);
    	
    	c.close();
    	return newMedia;
	}
	
	public List<MediaFile> getAllImageMedia() {
		List<MediaFile> mediaList = new ArrayList<MediaFile>();
		Cursor cursor = myDataBase.rawQuery(SELECT_IMG_MEDIA, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			MediaFile mediaFile = cursorToMedia(cursor);
			mediaList.add(mediaFile);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return mediaList;
	}
	
	public List<MediaFile> getAllAudioMedia() {
		List<MediaFile> mediaList = new ArrayList<MediaFile>();
		Cursor cursor = myDataBase.rawQuery(SELECT_AUD_MEDIA, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			MediaFile mediaFile = cursorToMedia(cursor);
			mediaList.add(mediaFile);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return mediaList;
	}
	
	public List<MediaFile> getAllMedia() {
		List<MediaFile> mediaList = new ArrayList<MediaFile>();
		Cursor cursor = myDataBase.rawQuery(SELECT_ALL_MEDIA, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			MediaFile mediaFile = cursorToMedia(cursor);
			mediaList.add(mediaFile);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return mediaList;
	}
	
	private MediaFile cursorToMedia(Cursor cursor){
		MediaFile mediaFile = new MediaFile();
		mediaFile.setId(cursor.getInt(0));
		mediaFile.setTeamid(cursor.getInt(1));
		mediaFile.setName(cursor.getString(2));
		mediaFile.setLocation(cursor.getString(3));
		mediaFile.setComment(cursor.getString(4));
		mediaFile.setGps_Lat(cursor.getFloat(5));
		mediaFile.setGps_Lon(cursor.getFloat(6));
		mediaFile.setMediaType(cursor.getInt(7));
		mediaFile.setIsStock(cursor.getInt(8));
		mediaFile.setIsUploaded(cursor.getInt(9));
		mediaFile.setDate_Time(cursor.getString(10));
		return mediaFile;
	}
        // Add your public helper methods to access and get content from the database.
       // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
       // to you to create adapters for your views.
 
}
